package dao;

import java.awt.GridLayout;

import java.io.FileOutputStream;
import java.io.IOException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.List;


import javax.swing.JFrame;
import javax.swing.JOptionPane;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import model.Student;
import model.Subject;
import model.Teacher;
import ui.BarChartPanel;
import ui.PieCharPanel;
import util.JDBCUtil;


public class TeacherDaoJDBCImpl implements TeacherDao{

	@Override
	public boolean isLogin(String identity, String name, String password) {
		boolean logincheck = false;
		Statement stat = null;
		ResultSet rs = null;
		String sql = "select * from teacher";// 表中有id和name这列
		try (Connection conn = JDBCUtil.getConnection()) {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while (rs.next()) {
				String tname = rs.getString("name");
				String pwd = rs.getString("password");
				if(name.equals(tname)) {
					if(password.equals(pwd)) {
						logincheck = true;
						System.out.println("该教师用户为："+name);
						break;
					}
				}
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return logincheck;
	}

	@Override
	public List<Student> getsourcebyname(String str) {
		List<Student> student = new ArrayList<>();
		Statement stat = null;
		ResultSet rs = null;
		String sql = "select * from student";// 表中有id和name这列
		try (Connection conn = JDBCUtil.getConnection()) {
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String classes = rs.getString("class");
				String gender = rs.getString("gender");
				String birthdate = rs.getString("birthdate");
				double score = rs.getDouble(str);
				if(score!=-1) {
					Student s = new Student(id,name,classes,gender,birthdate);
					Subject sub = new Subject(str,new Teacher());
					sub.setScore(score);
					s.getSublist().add(sub);
					student.add(s);
					System.out.println(id);
				}
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return student;
	}

	@Override
	public boolean checkhavecourse(String username, String str) {
		boolean check = false;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String sql = "select * from teacher where name like ?";//表中有id和name这列
		try (Connection conn = JDBCUtil.getConnection()) {
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, username);
			rs = pstat.executeQuery();
			while(rs.next()){
				double score = rs.getDouble(str);
				if(score!=-1) {
					check = true;
				}
			}
			
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return check;
	}

	@SuppressWarnings("resource")
	@Override
	public void ExportBycoursePOI(List<Student> student) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFCellStyle cellStyle = wb.createCellStyle(); 
        HSSFSheet sheet = wb.createSheet("学生成绩");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        HSSFDataFormat format = wb.createDataFormat();
        cellStyle.setDataFormat(format.getFormat("@")); 
        cell.setCellValue("学号");
        cell.setCellStyle(cellStyle);
        row.createCell(1).setCellValue("姓名");
        row.createCell(2).setCellValue("班级");
        row.createCell(3).setCellValue("科目");
        row.createCell(4).setCellValue("成绩");
        for (int i = 0; i < student.size(); i++) {
            //创建list.size()行数据
            row = sheet.createRow(i + 1);
            //把值一一写进单元格里
            //设置第一列为自动递增的序号
            row.createCell(0).setCellValue(student.get(i).getStudentID());
            row.createCell(1).setCellValue(student.get(i).getName());
            row.createCell(2).setCellValue(student.get(i).getClasses());
            row.createCell(3).setCellValue(student.get(i).getSublist().get(0).getName());
            row.createCell(4).setCellValue(student.get(i).getSublist().get(0).getScore());
        }
        
        try {
			FileOutputStream output = new FileOutputStream("E:\\JAKARTA.xls");
			wb.write(output);
			output.flush(); 
			JOptionPane.showMessageDialog(null, "导出成功!");
			System.out.println("成功创建excel文件");
		} catch (Exception e) {
			e.printStackTrace();
		}

		
	}

	@Override
	public List<Student> searchByname(String stuname) {
		List<Student> stulist = new ArrayList<>();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		//数据库模糊匹配的sql语法
		String sql = "select * from student where name like'%"+stuname+"%'" ;
		try (Connection conn = JDBCUtil.getConnection()) {
			pstat = conn.prepareStatement(sql);
			rs = pstat.executeQuery(sql);
			//创建ResultSetMetaData
			//获得ResultSet对象rs
			ResultSetMetaData data = rs.getMetaData();
			//调用方法得到表的列数
			int colum = rs.getMetaData().getColumnCount();
			String[] columnamestr = new String[colum];
			
			//获得表头,以实现图形界面动态建表
			for(int i = 7;i <= colum;i++) {
				columnamestr[i-1] = data.getColumnName(i);
				System.out.println(data.getColumnName(i));
			}
			//获取数据库数据
			while(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String classes = rs.getString("class");
				String gender = rs.getString("gender");
				String birthdate = rs.getString("birthdate");
				System.out.println(id+" "+name+" "+classes+" "+gender+" "+birthdate);
				double[] source = new double[colum];
				for(int j = 6;j < colum;j++) {
					source[j] = rs.getDouble(columnamestr[j]);
					System.out.println(source[j]);
				}
				//将课程信息包装成Subject类型
				List<Subject> subject = new ArrayList<>();
				for(int k = 6;k<colum;k++) {
					Subject s = new Subject(columnamestr[k],new Teacher(""));
					s.setScore(source[k]);
					subject.add(s);
				}
				//将所得数据包装成Student类型，
				Student student = new Student(id,name,classes,gender,birthdate);
				//加入课程信息
				student.setSublist(subject);
				//加入List
				stulist.add(student);
			}	
			
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return stulist;
	}

	@Override
	public List<Student> searchByid(int id) {
		List<Student> stulist = new ArrayList<>();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String sql = "select * from student where id like'"+id+"'" ;//表中有id和name这列
		try (Connection conn = JDBCUtil.getConnection()) {
			pstat = conn.prepareStatement(sql);
			rs = pstat.executeQuery(sql);
			ResultSetMetaData data = rs.getMetaData();
			int colum = rs.getMetaData().getColumnCount();
			String[] columnamestr = new String[colum];
			
			//SubjectDao sub = new SubjectDaoJDBCImpl();
			for(int i = 7;i <= colum;i++) {
				columnamestr[i-1] = data.getColumnName(i);
				System.out.println(data.getColumnName(i));
			}
			while(rs.next()){
				int stuid = rs.getInt("id");
				String name = rs.getString("name");
				String classes = rs.getString("class");
				String gender = rs.getString("gender");
				String birthdate = rs.getString("birthdate");
				System.out.println(stuid+" "+name+" "+classes+" "+gender+" "+birthdate);
				double[] source = new double[colum];
				for(int j = 6;j < colum;j++) {
					source[j] = rs.getDouble(columnamestr[j]);
					System.out.println(source[j]);
				}
				List<Subject> subject = new ArrayList<>();
				for(int k = 6;k<colum;k++) {
					Subject s = new Subject(columnamestr[k],new Teacher(""));
					s.setScore(source[k]);
					subject.add(s);
				}
				
				Student student = new Student(id,name,classes,gender,birthdate);
				student.setSublist(subject);
				stulist.add(student);
			}	
			
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return stulist;
	}

	@Override
	public List<Student> searchByclass(String classes) {
		List<Student> stulist = new ArrayList<>();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String sql = "select * from student where class like'"+classes+"'" ;//表中有id和name这列
		try (Connection conn = JDBCUtil.getConnection()) {
			pstat = conn.prepareStatement(sql);
			rs = pstat.executeQuery(sql);
			ResultSetMetaData data = rs.getMetaData();
			int colum = rs.getMetaData().getColumnCount();
			String[] columnamestr = new String[colum];
			
			//SubjectDao sub = new SubjectDaoJDBCImpl();
			for(int i = 7;i <= colum;i++) {
				columnamestr[i-1] = data.getColumnName(i);
				System.out.println(data.getColumnName(i));
			}
			while(rs.next()){
				int stuid = rs.getInt("id");
				String name = rs.getString("name");
				String classes1 = rs.getString("class");
				String gender = rs.getString("gender");
				String birthdate = rs.getString("birthdate");
				System.out.println(stuid+" "+name+" "+classes1+" "+gender+" "+birthdate);
				double[] source = new double[colum];
				for(int j = 6;j < colum;j++) {
					source[j] = rs.getDouble(columnamestr[j]);
					System.out.println(source[j]);
				}
				List<Subject> subject = new ArrayList<>();
				for(int k = 6;k<colum;k++) {
					Subject s = new Subject(columnamestr[k],new Teacher(""));
					s.setScore(source[k]);
					subject.add(s);
				}
				
				Student student = new Student(stuid,name,classes,gender,birthdate);
				student.setSublist(subject);
				stulist.add(student);
			}	
			
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return stulist;
	}

	@SuppressWarnings("resource")
	@Override
	public void ExportByclassPOI(List<Student> student) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFCellStyle cellStyle = wb.createCellStyle(); 
        HSSFSheet sheet = wb.createSheet("学生成绩");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        HSSFDataFormat format = wb.createDataFormat();
        cellStyle.setDataFormat(format.getFormat("@")); 
        int colum = 5+student.get(0).getSublist().size();
    	int rownum = student.size();
    	String [][] str = new String [rownum][colum];
    	String [] s = new String[colum];
    	s[0] = "学号";s[1] = "姓名";s[2] = "班级";s[3] = "性别";s[4] = "出生日期";
    	for(int k =0;k<student.get(0).getSublist().size();k++) {
    		s[k+5] = student.get(0).getSublist().get(k).getName();
    	}
    	for(int i = 0 ;i < rownum;i++) {
    		int index = student.get(i).getStudentID();
    		String str1 = String.valueOf(index);
    		str[i][0] = str1;
    		str[i][1] = student.get(i).getName();
    		str[i][2] = student.get(i).getClasses();
    		str[i][3] = student.get(i).getGender();
    		str[i][4] = student.get(i).getBirthdate();
			int r=0;
			for(int j = 5;j<colum;j++,r++) {
				double score = student.get(i).getSublist().get(r).getScore();
				str[i][j] = String.valueOf(score);
			}
		
    	}
    	cell.setCellValue("学号");
        cell.setCellStyle(cellStyle);
    	for(int i = 1;i<colum;i++)    { 
	        row.createCell(i).setCellValue(s[i]);
	    
		}
        for (int i = 0; i < rownum; i++) {
            //创建list.size()行数据
            row = sheet.createRow(i + 1);
            //把值一一写进单元格里
            //设置第一列为自动递增的序号
            for(int j = 0;j<colum;j++) {
	            row.createCell(j).setCellValue(str[i][j]);
            }
	            
        }
        
        try {
			FileOutputStream output = new FileOutputStream("E:\\JAKARTA.xls");
			wb.write(output);
			output.flush(); 
			JOptionPane.showMessageDialog(null, "导出成功!");
			System.out.println("成功创建excel文件");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public boolean updatescore(int id,String course, Object score) {
		boolean check = false;
		PreparedStatement pstat = null;
		int result = -1;
		
		String sql = "update student set "+course+"='"+score+"' where id='"+id+"'" ;//表中有id和name这列
		try (Connection conn = JDBCUtil.getConnection()) {
			pstat = conn.prepareStatement(sql);
			result = pstat.executeUpdate(sql);
			if(result>0) check = true;
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return check;
	}

	public boolean draw(List<Student> student) {
		boolean check = true;
		if(student.size()==0) check=false;
		
		int[][] x = new int[student.get(0).getSublist().size()][5]; //存柱值
		
		int[][] y = new int[student.get(0).getSublist().size()][2];//存饼值
		//初始化每条柱和每块饼的数值
		for(int i =0;i<student.get(0).getSublist().size();i++) {
			for(int j=0;j<5;j++) {
				x[i][j]=0;
			}
		}
		for(int i =0;i<student.get(0).getSublist().size();i++) {
			for(int j=0;j<2;j++) {
				y[i][j]=0;
			}
		}
		String [] names = new String[student.get(0).getSublist().size()];
		//遍历List<Student> student
		//对柱值和饼值进行赋值
		for(int i =0;i < student.get(0).getSublist().size();i++) {
			names[i] = student.get(0).getSublist().get(i).getName();
		}
		 for(int i=0;i<student.size();i++) {
		    	for(int j=0;j<student.get(i).getSublist().size();j++) {
		    		if(student.get(i).getSublist().get(j).getName().equals(names[j]))   {
		    			if(student.get(i).getSublist().get(j).getScore()>=0&&student.get(i).getSublist().get(j).getScore()<60) {
		    				x[j][0]++;y[j][0]++;
		    			}
		    			if(student.get(i).getSublist().get(j).getScore()>=60&&student.get(i).getSublist().get(j).getScore()<70) {
		    				x[j][1]++;y[j][1]++;
		    			}
		    			if(student.get(i).getSublist().get(j).getScore()>=70&&student.get(i).getSublist().get(j).getScore()<80) {
		    				x[j][2]++;y[j][1]++;
		    			}
		    			if(student.get(i).getSublist().get(j).getScore()>=80&&student.get(i).getSublist().get(j).getScore()<90) {
		    				x[j][3]++;y[j][1]++;
		    			}
		    			if(student.get(i).getSublist().get(j).getScore()>=90&&student.get(i).getSublist().get(j).getScore()<100) {
		    				x[j][4]++;y[j][1]++;
		    			}
		    		}
		    }
		 }      
			JFrame frame1=new JFrame("学生成绩柱形图");//
			frame1.setLayout(new GridLayout(2,2,10,10));
			for(int i = 0;i<student.get(i).getSublist().size();i++) {	
				frame1.add(new BarChartPanel(x[i][0],x[i][1],x[i][2],x[i][3],x[i][4],names[i]).getChartPanel());           
			}
			frame1.setBounds(50, 50, 800, 600);
			frame1.setVisible(true);
			JFrame frame2=new JFrame("学生成绩饼状图");
			frame2.setLayout(new GridLayout(2,2,10,10));
			for(int i = 0;i<student.get(i).getSublist().size();i++) {
				frame2.add(new PieCharPanel(student.get(i).getSublist().get(i).getName(),y[i][1],y[i][0]).getChartPanel());           
			}
			frame2.setBounds(50, 50, 800, 600);
			frame2.setVisible(true);
			
		return check;
	}
	
	
	
}
	


